package com.starbaba.commActivityStat.service;

import java.text.DecimalFormat;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.base.common.exception.AppException;
import com.starbaba.base.dao.LoanManagerBaseDao;
import com.tuanzi.server.loan_base.model.db.CollaborateChannelUser;
import com.tuanzi.server.loan_base.model.db.CommProductChannel;
import com.tuanzi.server.loan_base.model.db.CommProductPrdid;
import com.tuanzi.server.loan_base.model.db.stat.StatAppCommActivity;

public class CommActivityStatAppService extends LoanManagerBaseDao{
	
	private DecimalFormat decimalFormat1 = new DecimalFormat("#.##%");
	
	public String exportList(Map<String, Object> paramsMap, boolean checkIsPartner) throws Exception {
		List dataList = null;
		if(checkIsPartner) {
			dataList = getPartnerDataList(paramsMap, null, null);
		} else {
			dataList = getDataList(paramsMap, null, null);
		}
		StringBuilder sb = new StringBuilder("时间,产品,渠道,新激活uv,新注册uv,新登录,注册率,登录率uv\r\n");
		if(dataList == null) {
			return sb.toString();
		}
		for(int i=0; i<dataList.size(); i++) {
			StatAppCommActivity model = (StatAppCommActivity) dataList.get(i);
			sb.append(model.getTime() + ",")
			.append(model.getProdId()+",")
			.append(model.getChannel()+",")
			.append(model.getNewActivity()+",")
			.append(model.getNewRegister()+",")
			.append(model.getNewLogin()+",")
			.append(model.getPercentRegister()+",")
			.append(model.getPercentLogin()+"\r\n");
		}
		return sb.toString();
	}

	/**
	 * 运营数据列表
	 */
	public List getDataList(Map<String, Object> paramsMap,Integer pageNo, Integer pageSize) throws AppException {
		String channel = (String) paramsMap.get("channel");
		String prodId = (String) paramsMap.get("prdid");
		
		String whereSql = getWhereSql(paramsMap);
		StringBuffer groupSql = new StringBuffer(" group by a.time ");
		if(!"-1000".equals(channel)) {
			groupSql.append(",a.channel ");
		}
		if(!"-1000".equals(prodId)) {
			groupSql.append(",a.prod_id ");
		}
		
		StringBuffer listSql = new StringBuffer("SELECT a.time ,c.channel_name channel ,p.`name` prodId ,SUM(a.new_activity) new_activity ,SUM(a.new_register) new_register ,SUM(a.new_login) new_login "
				+ "FROM stat.stat_app_comm_activity a LEFT JOIN loan.comm_product_channel c ON a.channel = c.channel_code "
				+ "LEFT JOIN loan.comm_product_prdid p on a.prod_id = p.prdid ");
		listSql.append(whereSql)
			.append(groupSql)
			.append(" order by a.time desc ");
		if(pageNo != null && pageSize != null) {
			listSql.append(" limit " + pageNo + "," + pageSize);
		}
		
		List list =  getDao().list(listSql.toString(), StatAppCommActivity.class);
		if(list == null) {
			return null;
		}
		for(int i=0; i<list.size(); i++) {
			StatAppCommActivity model = (StatAppCommActivity)list.get(i);
			if("-1000".equals(channel)) {
				model.setChannel("全部");
			}
			if("-1000".equals(prodId)) {
				model.setProdId("全部");
			}
			
			Integer activityUv = model.getNewActivity();
			if(activityUv != null && activityUv != 0) {
				String percentRegister = decimalFormat1.format( model.getNewRegister() / activityUv.doubleValue());
				String percentLogin = decimalFormat1.format( model.getNewLogin() / activityUv.doubleValue());
				model.setPercentRegister(percentRegister);
				model.setPercentLogin(percentLogin);
			}
		}
		return list;
	}
	
	/**
	 * 合作方数据列表
	 */
	public List getPartnerDataList(Map<String, Object> paramsMap,Integer pageNo, Integer pageSize) throws Exception {
		String whereSql = getWhereSql(paramsMap);
		StringBuffer groupSql = new StringBuffer(" group by a.time ");
		
		StringBuffer listSqlSbf = new StringBuffer("SELECT a.time ,c.channel_name channel ,p.`name` prodId ,SUM(a.new_activity) new_activity ,SUM(a.new_register) new_register ,SUM(a.new_login) new_login "
				+ "FROM stat.stat_app_comm_activity_partner a LEFT JOIN loan.comm_product_channel c ON a.channel = c.channel_code "
				+ "LEFT JOIN loan.comm_product_prdid p on a.prod_id = p.prdid ");
		listSqlSbf.append(whereSql)
			.append(groupSql)
			.append(" order by a.time desc ");
		
		if(pageNo != null && pageSize != null) {
			listSqlSbf.append(" limit " + pageNo + "," + pageSize);
		}
		
		List list =  getDao().list(listSqlSbf.toString(), StatAppCommActivity.class);
		if(list == null) {
			return null;
		}
		for(int i=0; i<list.size(); i++) {
			StatAppCommActivity model = (StatAppCommActivity)list.get(i);
			
			Integer activityUv = model.getNewActivity();
			if(activityUv != null && activityUv != 0) {
				String percentRegister = decimalFormat1.format( model.getNewRegister() / activityUv.doubleValue());
				String percentLogin = decimalFormat1.format( model.getNewLogin() / activityUv.doubleValue());
				model.setPercentRegister(percentRegister);
				model.setPercentLogin(percentLogin);
			}
		}
		return list;
	}
	
	public int getPartnerCount(Map<String, Object> paramsMap) throws AppException {
		String whereSql = getWhereSql(paramsMap);
		StringBuffer groupSql = new StringBuffer(" group by a.time ");
		
		StringBuffer listSqlSbf = new StringBuffer("SELECT a.time ,c.channel_name channel ,p.`name` prodId ,SUM(a.new_activity) new_activity ,SUM(a.new_register) new_register ,SUM(a.new_login) new_login "
				+ "FROM stat.stat_app_comm_activity_partner a LEFT JOIN loan.comm_product_channel c ON a.channel = c.channel_code "
				+ "LEFT JOIN loan.comm_product_prdid p on a.prod_id = p.prdid ");
		listSqlSbf.append(whereSql)
			.append(groupSql);
		return getStatDao().getInt("select count(1) from ("+listSqlSbf+") a");
	}
	
	public int getCount(Map<String, Object> paramsMap) throws AppException {
		StringBuffer countSql = new StringBuffer("SELECT 1 FROM stat.stat_app_comm_activity a LEFT JOIN loan.comm_product_channel c ON a.channel = c.channel_code "
				+ "LEFT JOIN loan.comm_product_prdid p on a.prod_id = p.prdid ");
		StringBuffer groupSql = new StringBuffer(" group by a.time ");
		if(!"-1000".equals((String) paramsMap.get("channel"))) {
			groupSql.append(",a.channel ");
		}
		if(!"-1000".equals((String) paramsMap.get("prdid"))) {
			groupSql.append(",a.prod_id ");
		}
		countSql.append(getWhereSql(paramsMap));
		countSql.append(groupSql);
		String sql = "select count(1) from ("+countSql.toString()+") a";
		return getStatDao().getInt(sql);
	}
	
	private String getWhereSql(Map<String, Object> paramsMap) {
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String channel = (String) paramsMap.get("channel");
		String prodId = (String) paramsMap.get("prdid");
		
		StringBuffer whereSql = new StringBuffer(" where 1=1 ");
		if(StringUtils.isNotEmpty(startTime)) {
			whereSql.append(" and a.time >= '"+startTime+"' ");
		}
		if(StringUtils.isNotEmpty(endTime)) {
			whereSql.append(" and a.time <= '"+endTime+"' ");
		}
		if(StringUtils.isNotEmpty(channel) && !"-1000".equals(channel)) {
			whereSql.append(" and a.channel = '"+channel+"' ");
		}
		if(StringUtils.isNotEmpty(prodId) && !"-1000".equals(prodId)) {
			whereSql.append(" and a.prod_id = '"+prodId+"' ");
		}
		
		return whereSql.toString();
	}
	
	public List<CommProductChannel> getChannelList() throws AppException {
		return getDao().list("SELECT * FROM loan.comm_product_channel c",CommProductChannel.class);
	}
	
	public List<CommProductPrdid> getProdlList() throws AppException {
		return getDao().list("SELECT * FROM loan.comm_product_prdid ",CommProductPrdid.class);
	}
	
	/**
	 * 检查是否是合作方
	 * @return
	 * @throws AppException 
	 */
	public boolean checkIsPartner(String userId) throws AppException {
		String sql = "SELECT count(1) "
				+ "FROM loan_manager.sys_user u ,loan_manager.sys_usergroup_comid c ,loan_manager.sys_usergroup g "
				+ "WHERE g.groupname = '合作方' AND g.groupid = c.groupid  AND c.comid = u.comid AND u.userid = ? ";
		int count = getDao().getInt(sql, userId);
		if(count == 1) {
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * 合作方渠道
	 * @param userId
	 * @return
	 * @throws AppException
	 */
	public List<CommProductChannel> getPartnetChannel(String userId) throws AppException {
		CollaborateChannelUser model = new CollaborateChannelUser();
		model.setUsername(userId);
		List<CollaborateChannelUser> userChannelList = getStatDao().list(model, "username");
		if(userChannelList == null || userChannelList.size() == 0) {
			return null;
		}
		
		String sql = "SELECT * FROM loan.comm_product_channel c where 1=1 and c.channel_code in (#channel_list#)";
		StringBuffer sdf = new StringBuffer();
		for(CollaborateChannelUser channel : userChannelList) {
			if(sdf.length() == 0) {
				sdf.append(channel.getChannelId());
			}
			sdf.append(","+channel.getChannelId());
		}
		sql = sql.replaceFirst("#channel_list#",sdf.toString());
		
		List<CommProductChannel> channelList = getDao().list(sql, CommProductChannel.class);
		return channelList;
	}
	
	
}


























